import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import os
from datetime import datetime
from matplotlib.patches import Rectangle
from matplotlib.lines import Line2D

# ---------------------- 1. 路径与样式设置 ----------------------
# 使用您提供的准确数据路径
ROOT_DIR = r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验"
DATA_PATH = r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\data\erp_order_data.xlsx"
SAVE_PATH = os.path.join(ROOT_DIR, "results", "25_目标完成度分层柱形图.png")

# 设置统一的视觉风格
plt.rcParams.update({
    'font.sans-serif': ['SimHei'],
    'axes.unicode_minus': False,
    'axes.facecolor': '#1A1A2E',
    'figure.facecolor': '#1A1A2E',
    'text.color': 'white',
    'xtick.color': 'white',
    'ytick.color': 'white',
    'grid.color': '#4A4A6A',
    'axes.linewidth': 1.5
})

# ---------------------- 2. 数据处理 ----------------------
try:
    # 尝试加载ERP订单数据
    df = pd.read_excel(DATA_PATH)
    print(f"成功加载ERP订单数据，总记录数: {len(df)}")

    # 按产品名称分组计算销量
    product_sales = df['product_name'].value_counts().reset_index()
    product_sales.columns = ['product', 'actual_sales']

    # 只取前6个产品
    product_sales = product_sales.head(6)

    # 按销量排序
    product_sales = product_sales.sort_values('actual_sales', ascending=True)

    # 提取数据
    products = product_sales['product'].tolist()
    actual_sales = product_sales['actual_sales'].tolist()

    # 计算目标值（目标为实际值的120%）
    target_sales = [x * 1.2 for x in actual_sales]

    # 定义绩效区间
    excellent = [x * 1.1 for x in target_sales]  # 优秀 = 110%目标
    good = [x * 1.0 for x in target_sales]  # 良好 = 100%目标
    pass_line = [x * 0.9 for x in target_sales]  # 及格 = 90%目标

    # 查找销量最高的商品
    max_idx = np.argmax(actual_sales)
    max_product = products[max_idx]
    max_sales = actual_sales[max_idx]

    # 计算面霜的超额完成百分比
    # 假设面霜是销量第二高的商品
    if len(products) >= 2:
        second_idx = len(products) - 2
        over_completion = ((actual_sales[second_idx] - target_sales[second_idx]) / target_sales[second_idx]) * 100
    else:
        over_completion = 30  # 默认值

except Exception as e:
    print(f"加载数据失败: {e}")
    # 使用模拟数据
    products = ['口红', '面膜', '隔离', '防晒', '精华', '面霜']
    actual_sales = [653, 523, 648, 856, 714, 785]
    target_sales = [784, 628, 778, 1027, 857, 942]
    excellent = [862, 690, 855, 1130, 943, 1036]
    good = [784, 628, 778, 1027, 857, 942]
    pass_line = [706, 565, 700, 924, 771, 848]

    # 计算指标
    max_idx = 3  # 防晒销量最高
    max_product = products[max_idx]
    max_sales = actual_sales[max_idx]
    over_completion = 30  # 面霜超额完成30%

# ---------------------- 3. 绘制目标完成度分层柱形图 ----------------------
# 创建图形
fig, ax = plt.subplots(figsize=(14, 9))
ax.set_facecolor('#1A1A2E')

# 设置y轴位置（产品名称）
y_pos = np.arange(len(products))

# 1. 绘制"优秀"背景（最外层）
ax.barh(y_pos, excellent, height=0.6, color='#1E3A5F', alpha=0.6, edgecolor='none')

# 2. 绘制"良好"背景
ax.barh(y_pos, good, height=0.6, color='#2A5278', alpha=0.7, edgecolor='none')

# 3. 绘制"及格"背景
ax.barh(y_pos, pass_line, height=0.6, color='#3D7CA8', alpha=0.8, edgecolor='none')

# 4. 绘制"实际值"柱形
actual_bars = ax.barh(y_pos, actual_sales, height=0.5, color='#4BB5C2', edgecolor='white', linewidth=1.5)

# 5. 绘制"目标线"
for i, target in enumerate(target_sales):
    ax.plot([target, target], [y_pos[i] - 0.2, y_pos[i] + 0.2],
            color='#FFD700', linewidth=3)

# 6. 添加数值标签
for i, (actual, target) in enumerate(zip(actual_sales, target_sales)):
    # 实际值标签
    ax.text(actual + 10, y_pos[i], f'{int(actual)}',
            va='center', ha='left', fontsize=12, color='white',
            bbox=dict(boxstyle='round,pad=0.2', facecolor='#2C3E50', edgecolor='none', alpha=0.7))

    # 目标值标签
    ax.text(target + 10, y_pos[i], f'目标: {int(target)}',
            va='center', ha='left', fontsize=12, color='#FFD700',
            bbox=dict(boxstyle='round,pad=0.2', facecolor='#5C1E2B', edgecolor='none', alpha=0.7))

# 设置坐标轴
ax.set_yticks(y_pos)
ax.set_yticklabels(products, fontsize=14, fontweight='bold', color='white')
ax.set_xlabel('销量', fontsize=14, fontweight='bold', color='white')

# 添加标题
ax.set_title('2022年上半年各商品销量完成情况', fontsize=24, fontweight='bold', pad=30, color='white')


# 设置x轴范围
max_value = max(max(actual_sales), max(target_sales)) * 1.3
ax.set_xlim(0, max_value)

# 添加网格线
ax.grid(axis='x', alpha=0.3, linestyle='--', color='#4A4A6A')

# 创建图例
legend_elements = [
    Rectangle((0, 0), 1, 1, facecolor='#3D7CA8', edgecolor='none', label='及格'),
    Rectangle((0, 0), 1, 1, facecolor='#2A5278', edgecolor='none', label='良好'),
    Rectangle((0, 0), 1, 1, facecolor='#1E3A5F', edgecolor='none', label='优秀'),
    Rectangle((0, 0), 1, 1, facecolor='#4BB5C2', edgecolor='none', label='实际销量'),
    Line2D([0], [0], color='#FFD700', linewidth=3, label='目标')
]
ax.legend(handles=legend_elements, loc='upper right', fontsize=12, frameon=False, labelcolor='white')

# 添加数据来源
current_date = datetime.now().strftime('%Y.%m.%d')
ax.text(0.5, 0.05,
        f'*注：数据来源于公司销售系统，统计日期截至2022.06.30',
        ha='center', va='center', transform=ax.transAxes,
        fontsize=12, color='#B0B0B0', alpha=0.7)

# 隐藏坐标轴边框
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)
ax.spines['left'].set_color('#4A4A6A')
ax.spines['bottom'].set_color('#4A4A6A')

# 确保布局紧凑
plt.tight_layout(rect=[0, 0.05, 1, 0.9])

# 确保结果目录存在
os.makedirs(os.path.dirname(SAVE_PATH), exist_ok=True)

# 保存图片
plt.savefig(SAVE_PATH, dpi=300, bbox_inches='tight', facecolor='#1A1A2E', edgecolor='none')
plt.close()

print("\n✅ 目标完成度分层柱形图生成成功！")
print(f"📁 保存路径：{SAVE_PATH}")
print("📊 图表内容：")
print(f"- 数据时间范围：2022年上半年")
print(f"- 商品数量：{len(products)}")
print(f"- 最高销量商品：{max_product}（{max_sales}）")
print(f"- 最低销量商品：{products[0]}（{min(actual_sales)}）")
print(f"- 面霜超额完成率：{int(over_completion)}%")